package com.tieunguyen.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.tieunguyen.beans.Category;
import com.tieunguyen.beans.Story;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper{

	//private static String DB_PATH = "/data/data/com.tieunguyen.truyencuoi/";
	private static String DB_PATH = "";
	private static String DB_NAME = "data.db";
	Context context;
	SQLiteDatabase mDatabase;
	DatabaseHelper dbHelper;
	
	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		 if(android.os.Build.VERSION.SDK_INT >= 17){
		       DB_PATH = context.getApplicationInfo().dataDir + "/databases/";         
		    }
		    else
		    {
		    	//Log.d("namnd", context.getFilesDir().getParent());
		    	//DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
		    	DB_PATH = context.getFilesDir().getParent() + "/databases/";
		    }
		this.context = context;
		try {
            createDatabase();
        } catch (Exception ioe) {
            throw new Error("Unable to create database");
        }
	}

	public void createDatabase() throws IOException {
		// TODO Auto-generated method stub
		boolean check = isDBExist();
		if (!check){
			this.getReadableDatabase();
			try {
				copyDatabase();
			} catch (IOException e) {
				e.printStackTrace();
				throw new Error("ERROR COPPY DATABASE");
			}finally{
				this.close();
			}
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

	public boolean openDatabase() throws SQLException{
		String mPath = DB_PATH + DB_NAME;
		//mDatabase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.OPEN_READWRITE|SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		mDatabase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
		return mDatabase.isOpen();		
	}
	
	private void copyDatabase() throws IOException{
		InputStream inputStream = context.getAssets().open(DB_NAME);
		String outputFileName = DB_PATH + DB_NAME;
		OutputStream outputStream = new FileOutputStream(outputFileName);
		byte[] buffer = new byte[1024];
		int length;
		while ((length = inputStream.read(buffer)) > 0) {
			outputStream.write(buffer, 0, length);
		}
		outputStream.flush();
		outputStream.close();
		inputStream.close();
	}
	
	private boolean isDBExist() {
		SQLiteDatabase sqlDB = null;
		try {
			String dbPath = DB_PATH + DB_NAME;
			sqlDB = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READONLY|SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		} catch (SQLException e) {
			sqlDB = null;
		}
		if (sqlDB != null){
			sqlDB.close();
		}
		return sqlDB != null ? true : false;
	}
	
	public synchronized void close(){
        if(mDatabase != null)
            mDatabase.close();
        super.close();
    }

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}
	
	public ArrayList<Category> getCategory(Context mContext){
		
		ArrayList<Category> categories = new ArrayList<Category>();
		dbHelper = new DatabaseHelper(mContext);
		try {
			//String dbPath = "/data/data/" + mContext.getPackageName() + "/databases/" + DB_NAME;
			String dbPath = DB_PATH + DB_NAME;
			File f = new File(dbPath);
			if (!f.exists()) {
				String file = DB_NAME;
				OutputStream out = new FileOutputStream(dbPath);
				InputStream in = mContext.getAssets().open(file);
				
				byte[] buffer = new byte[1024];
		        int length;
		        
		        while ((length = in.read(buffer)) > 0) {
		              out.write(buffer, 0, length);
		         	}
		            in.close();	
	            out.close();
			}
			String sql = "SELECT * FROM categories";
			SQLiteDatabase db = dbHelper.getReadableDatabase();
			Cursor cursor = db.rawQuery(sql, null);
			
			//int count = 0;
			if (cursor.moveToFirst()){
				do{
					//count ++;
					Category category = new Category();
					int id = cursor.getInt(0);
					category.setId(id);
					String name = cursor.getString(1);
					category.setName(name);
					categories.add(category);
				}while (cursor.moveToNext());
				db.close();
				cursor.close();
				dbHelper.close();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return categories;
	}
	
	public ArrayList<Story> getStory(Context mContext, int catId){
		
		ArrayList<Story> stories = new ArrayList<Story>();
		dbHelper = new DatabaseHelper(mContext);
		try {
			String dbPath = DB_PATH + DB_NAME;
			File f = new File(dbPath);
			if (!f.exists()) {
				String file = DB_NAME;
				OutputStream out = new FileOutputStream(dbPath);
				InputStream in = mContext.getAssets().open(file);
				
				byte[] buffer = new byte[1024];
		        int length;
		        
		        while ((length = in.read(buffer)) > 0) {
		              out.write(buffer, 0, length);
		         	}
		            in.close();	
	            out.close();
			}
			String sql = "SELECT * FROM stories WHERE category = " + catId + " ORDER BY id DESC";
			SQLiteDatabase db = dbHelper.getReadableDatabase();
			Cursor cursor = db.rawQuery(sql, null);
			
			int count = 0;
			if (cursor.moveToFirst()){
				do{
					count ++;
					Story story = new Story();
					int id = cursor.getInt(0);
					story.setId(id);
					String title = cursor.getString(1);
					story.setTitle(title);
					String detail = cursor.getString(2);
					story.setDetail(detail);
					int category = cursor.getInt(3);
					story.setCategory(category);
					stories.add(story);
				}while (cursor.moveToNext());
				db.close();
				cursor.close();
				dbHelper.close();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return stories;
	}
	
	public JSONArray getStories(Context mContext, int catId){
		
		ArrayList<Story> stories = new ArrayList<Story>();
		
		JSONObject jsoResult = new JSONObject();
		JSONArray jsa = new JSONArray();
		JSONArray jsaResult = new JSONArray();
		dbHelper = new DatabaseHelper(mContext);
		try {
			String dbPath = DB_PATH + DB_NAME;
			File f = new File(dbPath);
			if (!f.exists()) {
				String file = DB_NAME;
				OutputStream out = new FileOutputStream(dbPath);
				InputStream in = mContext.getAssets().open(file);
				
				byte[] buffer = new byte[1024];
		        int length;
		        
		        while ((length = in.read(buffer)) > 0) {
		              out.write(buffer, 0, length);
		         	}
		            in.close();	
	            out.close();
			}
			String sql = "SELECT * FROM stories WHERE category = " + catId;
			SQLiteDatabase db = dbHelper.getReadableDatabase();
			Cursor cursor = db.rawQuery(sql, null);
			int count = 0;
			int page = 0;
			int c = 0;
			if (cursor.moveToFirst()){
				do{
					JSONObject jso = new JSONObject();
					count ++;
					Story story = new Story();
					int id = cursor.getInt(0);
					story.setId(id);
					String title = cursor.getString(1);
					story.setTitle(title);
					String detail = cursor.getString(2);
					story.setDetail(detail);
					int category = cursor.getInt(3);
					story.setCategory(category);
					stories.add(story);
					
					jso.put("id", id);
					jso.put("title", title);
					jso.put("detail", detail);
					jso.put("category", category);
					jsa.put(jso);
					
					if (count%10 == 0){
						c = (count/10);
						
						page ++;
					}


				}while (cursor.moveToNext());
				int r1 = 1;
				int r2 = 10;
				
				if (c>=1){
					for (int i=0; i<c; i++){
						JSONArray jsa1 = new JSONArray();
						JSONObject jsoResult1 = new JSONObject();
						for(int j=r1; j<=r2; j++){
							jsa1.put(jsa.get(j-1));
						}
						r1 += 10;
						r2 += 10;
						jsoResult1.put("page", i + 1);
						jsoResult1.put("content", jsa1);
						jsaResult.put(jsoResult1);
					}
					
					
				}
				
				if (count<10){
					jsoResult.put("page", 1);
					jsoResult.put("content", jsa);

					jsaResult.put(jsoResult);
				}

				if (count%10>0 && count>10){
					page ++;
					
					JSONArray jsa1 = new JSONArray();
					JSONObject jsoResult1 = new JSONObject();
					jsoResult1.put("page", page);
					int position = (page-1)*10;
					for(int i=position; i<jsa.length(); i++){
						jsa1.put(jsa.get(i-1));
					}
					jsoResult1.put("content", jsa1);
					jsaResult.put(jsoResult1);

				}
				
				db.close();
				cursor.close();
				dbHelper.close();
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		//return stories;
		
		//Log.d("namnd", ""+jsaResult.length());

		return jsaResult;
	}
}
